package com.forte.fortePoiUtils.reader.XSSFReader;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.forte.fortePoiUtils.reader.ExcelBean;
import com.forte.fortePoiUtils.reader.ExcelReader;

/**
 * XSSF操作对象
 * @author ForteScarlet
 * <div>
 *		<ul>
 *  		<li> ---><a href='https://gitee.com/ForteScarlet' target='_block'>码云个人地址</a></li> </li>
 *		</ul>
 * </div>
 */
public class XSSFReader extends ExcelReader {

    /**
     * 构造方法
     */
    public XSSFReader(InputStream inputStream) {
        super(inputStream);
    }

    /**
     * 构造方法
     */
    public XSSFReader(File file) throws FileNotFoundException {
        super(new FileInputStream(file));
    }

    /**
     * 读取数据并返回封装类
     * @param beanClass
     * javaBean的Class对象
     * @param result
     * 表头与字段的映射
     * @param sheetName
     * 表名
     * @param <T>
     * @return 获取结果
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IOException
     * @throws InvocationTargetException
     */
    @Override
    public <T> ExcelBean<T> read(Class<T> beanClass, Map<String, String> result, String sheetName) throws InstantiationException, IllegalAccessException, IOException, InvocationTargetException {

        int sheetIndex;

        // 保存数据的集合
        List<T> list = new ArrayList<>();

        // 返回用的封装类
        ExcelBean<T> excelBean = getExcelBean(beanClass);
        excelBean.setList(list);

        // 1 获得工作簿
        Workbook book = new XSSFWorkbook(getStream());

        // 获取表格
        Sheet sheet;
        if (sheetName != null) {
            sheet = book.getSheet(sheetName);
            sheetIndex = book.getSheetIndex(sheetName);
        } else {
            sheet = book.getSheetAt(0);
            sheetIndex = 0;
        }

        // 封装数据
        excelBean.setSheetIndex(sheetIndex);
        excelBean.setSheetName(sheetName);

        // 开始行
        int firstRowNum = sheet.getFirstRowNum();
        // 结束行
        int lastRowNum = sheet.getLastRowNum();


        //获取合并了的单元格，默认认为第一个合并单元格为标题
//        int numMergedRegions = sheet.getNumMergedRegions();
//        boolean hasHead = false;
//        String titleRegion =null;
//        String title = "";
//        if(numMergedRegions > 0){
//            //如果有标题，保存
//            CellRangeAddress mergedRegion = sheet.getMergedRegion(0);
//            titleRegion = mergedRegion.formatAsString();
//        }


        // 先获取表头
        Row headRow = sheet.getRow(firstRowNum);

//        Cell headCell = headRow.getCell(0);


//        if(headCell.getArrayFormulaRange().formatAsString().equals(titleRegion)){
//            //是表头
//            title =headCell.getStringCellValue();
//            headRow = sheet.getRow(++firstRowNum);
//        }
//

            // 开始列
            short firstCellNum = headRow.getFirstCellNum();
            // 结束列
            short lastCellNum = headRow.getLastCellNum();

            // 保存字段与列顺序的对应
            List<String> fields = new ArrayList<>();


            for (int i = firstCellNum; i < lastCellNum; i++) {
                //将单元格设置为String类型
                Cell cell = headRow.getCell(i);

            if (cell != null) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                fields.add(cell.getStringCellValue());
            }
        }

        // 如果有映射，添加映射
        if (result != null)
            fields = fields.stream().map(f -> result.get(f) != null ? result.get(f) : f).collect(Collectors.toList());

        // 遍历剩下的数据行
        for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
            // 每一个javaBean的数据
            Map<String, String> data = new HashMap<>();

            // 数据行
            Row row = sheet.getRow(i);
            // 遍历数据行的每个单元格并添加数据
            Iterator<String> iterator = fields.iterator();
            for (int j = firstCellNum; j < lastCellNum; j++) {
                String key = "";
                try {
                    key = iterator.next();
                } catch (Exception e) {
                }
                //将单元格设置为String类型
                String value = null;
                if(row == null){
                    continue;
                }
                Cell cell = row.getCell(j);
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    value = cell.getStringCellValue();
                }
                data.put(key, value);
            }
            // 遍历结束后,使用BeanUtils工具封装javaBean
            T bean = beanClass.newInstance();
            BeanUtils.populate(bean, data);
            list.add(bean);
        }

        /* 返回结果 */
        return excelBean;

    }

    /**
     * 读取数据并返回封装类
     * @param beanClass
     * javaBean的Class对象
     * @param result
     * 表头与字段的映射
     * @param sheetIndex
     * 表索引
     * @param <T>   泛型
     * @return 获取到的数据
     * @throws FileNotFoundException
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    @Override
    public <T> ExcelBean<T> read(Class<T> beanClass, Map<String, String> result, int sheetIndex) throws FileNotFoundException, IOException, InstantiationException, IllegalAccessException, InvocationTargetException {

        String sheetName;

        // 保存数据的集合
        List<T> list = new ArrayList<>();

        // 返回用的封装类
        ExcelBean<T> excelBean = getExcelBean(beanClass);
        excelBean.setList(list);

        // 1 获得工作簿
        Workbook book = new XSSFWorkbook(getStream());

        // 获取表格
        Sheet sheet;
        if (sheetIndex < 0)
            sheet = book.getSheetAt(0);
        else
            sheet = book.getSheetAt(sheetIndex);

        //获取表名
        sheetName = sheet.getSheetName();
        // 封装数据
        excelBean.setSheetIndex(sheetIndex);
        excelBean.setSheetName(sheetName);

        // 开始行
        int firstRowNum = sheet.getFirstRowNum();
        // 结束行
        int lastRowNum = sheet.getLastRowNum();



        //获取合并了的单元格，默认认为第一个合并单元格为标题
//        int numMergedRegions = sheet.getNumMergedRegions();
//        boolean hasHead = false;
//        String titleRegion =null;
//        String title = "";
//        if(numMergedRegions > 0){
//            //如果有标题，保存
//            CellRangeAddress mergedRegion = sheet.getMergedRegion(0);
//            titleRegion = mergedRegion.formatAsString();
//        }


        // 先获取表头
        Row headRow = sheet.getRow(firstRowNum);
//        Cell headCell = headRow.getCell(0);
//        if(headCell.getArrayFormulaRange().formatAsString().equals(titleRegion)){
//            //是表头
//            title =headCell.getStringCellValue();
//            headRow = sheet.getRow(++firstRowNum);
//        }


        // 开始列
        short firstCellNum = headRow.getFirstCellNum();
        // 结束列
        short lastCellNum = headRow.getLastCellNum();

        // 保存字段与列顺序的对应
        List<String> fields = new ArrayList<>();



        for (int i = firstCellNum; i < lastCellNum; i++) {
            //将单元格设置为String类型
            Cell cell = headRow.getCell(i);

            if (cell != null) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                fields.add(cell.getStringCellValue());
            }
        }

        // 如果有映射，添加映射
        if (result != null)
            fields = fields.stream().map(f -> result.get(f) != null ? result.get(f) : f).collect(Collectors.toList());

        // 遍历剩下的数据行
        for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
            // 每一个javaBean的数据
            Map<String, String> data = new HashMap<>();

            // 数据行
            Row row = sheet.getRow(i);
            // 遍历数据行的每个单元格并添加数据
            Iterator<String> iterator = fields.iterator();
            for (int j = firstCellNum; j < lastCellNum; j++) {
                String key = "";
                try {
                    key = iterator.next();
                } catch (Exception e) {
                }
                //将单元格设置为String类型
                String value = null;
                Cell cell = row.getCell(j);
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    value = cell.getStringCellValue();
                }
                data.put(key, value);
            }
            // 遍历结束后,使用BeanUtils工具封装javaBean
            T bean = beanClass.newInstance();
            BeanUtils.populate(bean, data);
            list.add(bean);
        }

        // 返回结果
        return excelBean;
    }

}
